# -*- coding: utf-8 -*-
import cx_Oracle
import logging
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'

class MyConnectOracle():

    def __init__(self,username,password,ip,service_name):
        self._username = username
        self._password = password
        self._ip = ip
        self._service_name = service_name
        self._conn = None
        self._Connect()

    def _Connect(self):
        try:
            if None == self._conn :
                self._conn = cx_Oracle.connect(self._username + '/' + self._password + '@' + self._ip + '/' + self._service_name)  # 连接数据库
            else:
                pass
        except cx_Oracle.DatabaseError as msg:
                print(msg)
                logging.debug(msg)
        except Exception:
            print(Exception)

    def __del__(self):
        try:
            if  self._conn:
                self._conn.close()
                self._conn = None
            else:
                pass
        except Exception:
            print(Exception)

    def _NewCursor(self):
        try:
            cursor = self._conn.cursor()
            if cursor:
                return cursor
            else:
                print('error cursor')
                return None
        except Exception:
            print(Exception)

    def _DelCursor(self,cursor):
        try:

            if cursor:
                cursor.close()
            else:
                print(' cursor is gone')
                pass
        except Exception:
            print(Exception)



#judge if execute is right
    def _PermitedUpdateSql(self,sql):
        state = True
        lrsql = sql.upper()
        sql_elems = [lrsql.strip().split()]
        #at least update, delete have 4 words
        if len(sql_elems) < 4:
            state = False
        #更新删除语句，判断首单词，不带where语句的sql不予执行
        elif sql_elems[0] in ['UPDATE','DELETE']:
            if 'WHERE' not in sql_elems:
                state = False
        return state

    def Query(self,sql,nStart = 0,nNum = -1):
        rt = []

        #获取cursor
        cursor = self._NewCursor()
        if None == cursor:
            return  rt

        #查询到列表
        try:
            cursor.execute(sql)
            if( nStart == 0)and (nNum == 1):
                rt.append(cursor.fetchone())
            else:
                rs = cursor.fetchall()
                if nNum == -1:
                    rt.extend(rs[nStart:])
                else:
                    rt.extend(rs[nStart:nStart +nNum])

        except cx_Oracle.DatabaseError as msg:
            print(msg)
            logging.debug(msg)
        #释放cursor
        self._DelCursor(cursor)

        return rt

    #更新操作
    def Exec(self,sql):

        #获取cursor
        rt = None
        cursor = self._NewCursor()
        if not cursor:
            return rt

        #判断sql是否允许其执行
        if not _PermitedUpdateSql(sql):
            return rt

        #执行语句
        rt = cursor.execute(sql)
        #释放cursor
        self._DelCursor(cursor)

        return rt

    def Export(self,sql,filename,colfg = ','):
        rt = self.Query(sql)
        if rt:
            with open(filename,'a') as file:
                for row in rt:
                    line = ''
                    for col in row:
                        line +=str(col)+colfg
                    line +='\n'
                    file.write(line)


    def insert(self,sql,list):

        try:
            # 获取cursor
            rt = None
            cursor = self._NewCursor()
            if not cursor:
                return rt


            sql = '''insert into table(col,...) values(:1,:2,....)'''

                # cursor.prepare(sql)
            cursor.executemany(sql, list)

            cursor.commit()
        except cx_Oracle.DatabaseError as msg:
            print(msg)
        finally:
            cursor.close()

from MyConnectOracle import MyConnectOracle

if __name__ == "__main__":
    username = ''
    password = ''
    ip = '192....'
    service_name = '...'

    myconnect =  MyConnectOracle(username,password,ip,service_name)
 
    sqlstr = '''select * from Subway where stationname like ('%'||' 西安西站'||'%'）'''
    myconnect.Export(sqlstr,'test1.txt')